﻿using Kingdee.BOS.App.Data;
using Kingdee.BOS;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DM.Report
{
    [HotUpdate]
    [Description("价格分析表")]
    public class PriceAnalysis : SysReportBaseService
    {
        /// 初始化事件：在此事件中，设置报表基本属性
        public override void Initialize()
        {
            base.Initialize();
            // 简单账表类型：普通、树形、分页
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.BillKeyFieldName = "FMATERIALNO";
            // 报表名称
            this.ReportProperty.ReportName = new LocaleValue("价格分析表", base.Context.UserLocale.LCID);
            this.IsCreateTempTableByPlugin = true;
            this.ReportProperty.IsUIDesignerColumns = true;
            this.ReportProperty.IsGroupSummary = true;
            this.ReportProperty.SimpleAllCols = false;
            // 单据主键：两行FID相同，则为同一单的两条分录，单据编号可以不重复显示
            this.ReportProperty.PrimaryKeyFieldName = "FMATERIALNO";
            this.ReportProperty.IsDefaultOnlyDspSumAndDetailData = true;
            // 报表主键字段名：默认为FIDENTITYID，可以修改
            this.ReportProperty.IdentityFieldName = "FIDENTITYID";
            // 设置精度控制
            //List list = new List();
            //// 数量
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FQty",
            //    DecimalControlFieldName = "FUnitPrecision"
            //});
            //// 单价
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FTAXPRICE",
            //    DecimalControlFieldName = "FPRICEDIGITS"
            //});
            //// 金额
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FALLAMOUNT",
            //    DecimalControlFieldName = "FAMOUNTDIGITS"
            //});
            //this.ReportProperty.DecimalControlFieldList = list;
        }

        public override string GetTableName()
        {
            var result = base.GetTableName();
            return result;
        }

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);
            // 拼接过滤条件 ： filter
            // 略
            // 默认排序字段：需要从filter中取用户设置的排序字段
            string seqFld = string.Format(base.KSQL_SEQ, "FMATERIALNO");

            DateTime FDate;
            DateTime FEndDate;
            int YEAR = 0;
            int LASTYEAR = 0;
            int MONTH = 0;
            int LASTMONTH = 0;
            string FMaterialid_idsum = "", FSuppiler_idsum = "", FPP_idsum = "";

            Kingdee.BOS.Orm.DataEntity.DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            StringBuilder SQLFilter = new StringBuilder();
            StringBuilder SQLFilter1 = new StringBuilder();
            FDate = Convert.ToDateTime(dyFilter["FDate"]);//期间
            //FEndDate = Convert.ToDateTime(dyFilter["FEndDate"]);//最后发货日期
            //过滤 日期
            if (!FDate.ToString().Contains("0001"))
            {
                //SQLFilter.Append("and t.FDATE>='" + FStartDate + "' and t.FDATE<='" + FEndDate + "'");
                YEAR = FDate.Year;
                MONTH = FDate.Month;
                LASTYEAR = FDate.AddYears(-1).Year;
                LASTMONTH = FDate.AddMonths(-1).Month;
            }
            DynamicObjectCollection dyCollection = dyFilter["FMATERIALID"] as DynamicObjectCollection;
            if (dyCollection.Count > 0)
            {
                foreach (var dobj in dyCollection)
                {
                    //string FMaterialid_id = Convert.ToString(dobj["FMATERIALID_ID"]);
                    DynamicObject d = dobj["FMATERIALID"] as DynamicObject;
                    string FMaterialidNumber = d["NUMBER"].ToString();
                    FMaterialid_idsum = FMaterialid_idsum + "'" + FMaterialidNumber + "'" + ",";

                }
                SQLFilter.Append("and FMATERIALNO IN (" + FMaterialid_idsum.TrimEnd(',') + ")");
            }
            //DynamicObjectCollection dyCollection1 = dyFilter["FSUPPLIERID"] as DynamicObjectCollection;
            //if (dyCollection1.Count > 0)
            //{
            //    foreach (var dobj in dyCollection1)
            //    {
            //        //string FSuppiler_id = Convert.ToString(dobj["FSUPPLIERID_id"]);
            //        DynamicObject d = dobj["FSUPPLIERID"] as DynamicObject;
            //        string FSUPPLIERIDNumber = d["Number"].ToString();
            //        FSuppiler_idsum = FSuppiler_idsum + "'" + FSUPPLIERIDNumber + "'" + ",";
            //    }
            //    SQLFilter.Append("and t25.FSUPPLIERNUMBER IN (" + FSuppiler_idsum.TrimEnd(',') + ")");
            //}
            //DynamicObjectCollection dyCollection2 = dyFilter["FPP"] as DynamicObjectCollection;
            //if (dyCollection2.Count > 0)
            //{
            //    foreach (var dobj in dyCollection2)
            //    {
            //        string FPP_id = Convert.ToString(dobj["FPP_id"]);
            //        FPP_idsum = FPP_idsum + "'" + FPP_id + "'" + ",";
            //    }
            //    SQLFilter.Append("and t6.FENTRYID IN (" + FPP_idsum.TrimEnd(',') + ")");
            //}
            if (!Convert.ToString(dyFilter["FPriceType"]).Equals(""))
            {
                SQLFilter.Append("and FPRICETYPE='" + dyFilter["FPriceType"] + "'");
            }
            //if (!Convert.ToString(dyFilter["FGroup_id"]).Equals("0"))
            //{
            //    SQLFilter.Append("and t2.FMATERIALGROUP='" + dyFilter["FGroup_id"] + "'");
            //}
            //if (!Convert.ToString(dyFilter["FBillNo"]).Equals(""))
            //{
            //    SQLFilter1.Append("and t.FBillNo='" + dyFilter["FBillNo"] + "'");
            //}

            // 取数SQL
            string sql = string.Format(@"/*dialect*/
------------------------------------------当月最高价--------------------------------------------------------------
select FNUMBER,FNAME,max(FTAXPRICE)FTAXPRICE,FPriceType into #HYearNowmonth  from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where
dateadd(dd,-day('{3}-{4}-01')+1,'{3}-{4}-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{3}-{4}-01'),dateadd(m,1,'{3}-{4}-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{3}-{4}-01')+1,'{3}-{4}-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-{4}-01'),dateadd(m,1,'{3}-{4}-11'))) and T.FEXPIRYDATE='2100-01-01')
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{3}-{4}-01')+1,'{3}-{4}-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{3}-{4}-01'),dateadd(m,1,'{3}-{4}-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-{4}-01')+1,'{3}-{4}-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{3}-{4}-01')+1,'{3}-{4}-10'))
   )t
   --where t.row_num=1
   group by FNUMBER,FNAME,FPriceType
------------------------------------------历史最低价--------------------------------------------------------------
select FNUMBER,FNAME,min(FTaxPrice) FTaxPrice,FPriceType into #minYearNow  from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
)t
--where t.row_num=1
group by FNUMBER,FNAME,FPriceType
------------------------------------------历史最高价--------------------------------------------------------------
select FNUMBER,FNAME,max(FTaxPrice) FTaxPrice,FPriceType INTO #maxYearNow from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
)t
--where t.row_num=1
group by FNUMBER,FNAME,FPriceType

------------------------------------------上月最高价--------------------------------------------------------------
select FNUMBER,FNAME,max(FTAXPRICE)FTAXPRICE,FPriceType into #HYearNowLastmonth from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where
dateadd(dd,-day('{3}-{6}-01')+1,'{3}-{6}-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{3}-{6}-01'),dateadd(m,1,'{3}-{6}-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{3}-{6}-01')+1,'{3}-{6}-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-{6}-01'),dateadd(m,1,'{3}-{6}-11'))) and T.FEXPIRYDATE='2100-01-01')
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{3}-{6}-01')+1,'{3}-{6}-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{3}-{6}-01'),dateadd(m,1,'{3}-{6}-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-{6}-01')+1,'{3}-{6}-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{3}-{6}-01')+1,'{3}-{6}-10'))
   )t
   --where t.row_num=1
   group by FNUMBER,FNAME,FPriceType

------------------------------------------去年当月最高价--------------------------------------------------------------
select FNUMBER,FNAME,max(FTAXPRICE)FTAXPRICE,FPriceType into #HLastYearNowmonth from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where
dateadd(dd,-day('{5}-{4}-01')+1,'{5}-{4}-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{5}-{4}-01'),dateadd(m,1,'{5}-{4}-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{5}-{4}-01')+1,'{5}-{4}-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{5}-{4}-01'),dateadd(m,1,'{5}-{4}-11'))) and T.FEXPIRYDATE='2100-01-01')
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{5}-{4}-01')+1,'{5}-{4}-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{5}-{4}-01'),dateadd(m,1,'{5}-{4}-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{5}-{4}-01')+1,'{5}-{4}-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{5}-{4}-01')+1,'{5}-{4}-10'))
   )t
   --where t.row_num=1
   group by FNUMBER,FNAME,FPriceType

select distinct 
CONVERT(VARCHAR(100), tz.FCREATEDATE,23)FEFFECTIVEDATE,
CASE WHEN Tz.F_ORA_GOODSTATUS='1' THEN '正常' 
WHEN Tz.F_ORA_GOODSTATUS='2' THEN '淘汰' end FState,
CASE WHEN T25.FPRICETYPE='2' THEN '采购' 
WHEN T25.FPRICETYPE='3' THEN '委外' else '' end FPRICETYPE,
t25.FSUPPLIER,
Tz.FNUMBER FMATERIALNO,
t6.FDATAVALUE FPP,
T8.FNAME CC,
t9.FNAME FMATERIALNAME,
T9.FSPECIFICATION,
t11.FNAME FUNIT,
T12.FTAXPRICE FHYearNowmonth,
t13.FTaxPrice FminYearNow,
t14.FTaxPrice FmaxYearNow,
t15.FTaxPrice FHYearNowLastmonth,
t16.FTaxPrice FHLastYearNowmonth,
T25.FPRICETYPE FPRICETYPEJOIN
INTO #TABLE
from T_BD_MATERIAL tz
LEFT JOIN (
SELECT distinct T2.FNUMBER,T3.FNAME FSUPPLIER,T4.FNUMBER FSUPPLIERNUMBER,t1.FPriceType 
FROM  t_PUR_PriceListEntry T 
LEFT JOIN t_PUR_PriceList T1 ON T.FID=T1.FID
LEFT JOIN T_BD_MATERIAL T2 ON T.FMATERIALID=T2.FMATERIALID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T1.FSUPPLIERID=T3.FSUPPLIERID
LEFT JOIN T_BD_SUPPLIER T4 ON T3.FSUPPLIERID=T4.FSUPPLIERID
)T25 ON TZ.FNUMBER=T25.FNUMBER
left join T_BAS_ASSISTANTDATAENTRY_L t6 on tz.F_ora_PP=t6.FENTRYID
LEFT JOIN T_BD_MATERIALGROUP T7 ON Tz.FMATERIALGROUP=T7.FID
LEFT JOIN T_BD_MATERIALGROUP_L T8 ON T7.FID=T8.FID
left join T_BD_MATERIAL_L t9 on tz.FMATERIALID=t9.FMATERIALID
left join t_BD_MaterialBase t10 on tz.FMATERIALID=t10.FMATERIALID
left join T_BD_UNIT_L t11 on t10.FBASEUNITID=t11.FUNITID
left join #HYearNowmonth t12 on t25.FNUMBER=T12.FNUMBER and t25.FSUPPLIER=t12.FNAME and t25.FPRICETYPE=t12.FPRICETYPE
left join #minYearNow t13 on t25.FNUMBER=t13.FNUMBER and t25.FSUPPLIER=t13.FNAME and t25.FPRICETYPE=t13.FPRICETYPE
left join #maxYearNow t14 on t25.FNUMBER=t14.FNUMBER and t25.FSUPPLIER=t14.FNAME and t25.FPRICETYPE=t14.FPRICETYPE
left join #HYearNowLastmonth t15 on t25.FNUMBER=t15.FNUMBER and t25.FSUPPLIER=t15.FNAME and t25.FPRICETYPE=t15.FPRICETYPE
left join #HLastYearNowmonth t16 on t25.FNUMBER=t16.FNUMBER and t25.FSUPPLIER=t16.FNAME and t25.FPRICETYPE=t16.FPRICETYPE

UPDATE T SET T.FHYearNowmonth=T1.FTAXPRICE   FROM #TABLE T
LEFT JOIN #HYearNowmonth T1 ON T.FMATERIALNO=T1.FNUMBER AND T.FPRICETYPEJOIN=T1.FPRICETYPE 
--LEFT JOIN #minYearNow T2 ON T.FMATERIALNO=T2.FNUMBER AND T.FPRICETYPEJOIN=T2.FPRICETYPE 
--LEFT JOIN #maxYearNow T3 ON T.FMATERIALNO=T3.FNUMBER AND T.FPRICETYPEJOIN=T3.FPRICETYPE 
--LEFT JOIN #HYearNowLastmonth T4 ON T.FMATERIALNO=T4.FNUMBER AND T.FPRICETYPEJOIN=T4.FPRICETYPE 
--LEFT JOIN #HLastYearNowmonth T5 ON T.FMATERIALNO=T5.FNUMBER AND T.FPRICETYPEJOIN=T5.FPRICETYPE 
where t.FSUPPLIER is null and t1.FNAME is null 

UPDATE T SET T.FminYearNow=T2.FTAXPRICE   FROM #TABLE T
LEFT JOIN #minYearNow T2 ON T.FMATERIALNO=T2.FNUMBER AND T.FPRICETYPEJOIN=T2.FPRICETYPE 
where t.FSUPPLIER is null and t2.FNAME is null 

UPDATE T SET T.FmaxYearNow=T3.FTAXPRICE   FROM #TABLE T
LEFT JOIN #maxYearNow T3 ON T.FMATERIALNO=T3.FNUMBER AND T.FPRICETYPEJOIN=T3.FPRICETYPE 
where t.FSUPPLIER is null and t3.FNAME is null 

UPDATE T SET T.FHYearNowLastmonth=T4.FTAXPRICE   FROM #TABLE T
LEFT JOIN #HYearNowLastmonth T4 ON T.FMATERIALNO=T4.FNUMBER AND T.FPRICETYPEJOIN=T4.FPRICETYPE 
where t.FSUPPLIER is null and t4.FNAME is null 

UPDATE T SET T.FHLastYearNowmonth=T5.FTAXPRICE   FROM #TABLE T
LEFT JOIN #HLastYearNowmonth T5 ON T.FMATERIALNO=T5.FNUMBER AND T.FPRICETYPEJOIN=T5.FPRICETYPE 
where t.FSUPPLIER is null and t5.FNAME is null 

select distinct *,
CONVERT(VARCHAR(255),CAST((FHYearNowmonth-FminYearNow)/FHYearNowmonth*100 AS decimal(10,2)))+'%' FminYearNowBL,
CONVERT(VARCHAR(255),CAST((FHYearNowmonth-FmaxYearNow)/FHYearNowmonth*100 AS decimal(10,2)))+'%' FmaxYearNowBL,
CONVERT(VARCHAR(255),CAST((FHYearNowmonth-FHYearNowLastmonth)/FHYearNowmonth*100 AS decimal(10,2)))+'%' FHYearNowLastmonthBL,
CONVERT(VARCHAR(255),CAST((FHYearNowmonth-FHLastYearNowmonth)/FHYearNowmonth*100 AS decimal(10,2)))+'%' FHLastYearNowmonthBL,
{0} INTO {1}
from #TABLE 
WHERE FMATERIALNO IS NOT NULL {2}
order by FSUPPLIER
                                        ", seqFld, tableName, SQLFilter, YEAR,MONTH, LASTYEAR,LASTMONTH);
            DBUtils.ExecuteDynamicObject(this.Context, sql);
        }

        protected override string GetIdentityFieldIndexSQL(string tableName)
        {
            string result = base.GetIdentityFieldIndexSQL(tableName);
            return result;
        }

        protected override void ExecuteBatch(List<string> listSql)
        {
            base.ExecuteBatch(listSql);
        }

        protected override string GetSummaryColumsSQL(List<SummaryField> summaryFields)
        {
            var result = base.GetSummaryColumsSQL(summaryFields);
            return result;
        }

        protected override System.Data.DataTable GetListData(string sSQL)
        {
            var result = base.GetListData(sSQL);
            return result;
        }

        protected override System.Data.DataTable GetReportData(IRptParams filter)
        {
            var result = base.GetReportData(filter);
            return result;
        }

        protected override System.Data.DataTable GetReportData(string tablename, IRptParams filter)
        {
            var result = base.GetReportData(tablename, filter);
            return result;
        }

        public override int GetRowsCount(IRptParams filter)
        {
            var result = base.GetRowsCount(filter);
            return result;
        }

        protected override string BuilderFromWhereSQL(IRptParams filter)
        {
            string result = base.BuilderFromWhereSQL(filter);
            return result;
        }

        protected override string BuilderSelectFieldSQL(IRptParams filter)
        {
            string result = base.BuilderSelectFieldSQL(filter);
            return result;
        }

        protected override string BuilderTempTableOrderBySQL(IRptParams filter)
        {
            string result = base.BuilderTempTableOrderBySQL(filter);
            return result;
        }

        public override void CloseReport()
        {
            base.CloseReport();
        }

        protected override string CreateGroupSummaryData(IRptParams filter, string tablename)
        {
            string result = base.CreateGroupSummaryData(filter, tablename);
            return result;
        }

        protected override void CreateTempTable(string sSQL)
        {
            base.CreateTempTable(sSQL);
        }

        public override void DropTempTable()
        {
            base.DropTempTable();
        }

        public override System.Data.DataTable GetList(IRptParams filter)
        {
            var result = base.GetList(filter);
            return result;
        }

        public override List<long> GetOrgIdList(IRptParams filter)
        {
            var result = base.GetOrgIdList(filter);
            return result;
        }

        public override List<Kingdee.BOS.Core.Metadata.TreeNode> GetTreeNodes(IRptParams filter)
        {
            var result = base.GetTreeNodes(filter);
            return result;
        }
    }
}
